import openpyxl
from openpyxl.utils import get_column_letter
from datetime import datetime, date
import random
import pymysql
import string


# ================= 生成测试数据 =================
def generate_mock_data():
    data = []
    for i in range(1, 2001):  # 2000行数据
        row = [
            i,  # id
            ''.join(random.choices(string.digits, k=18)),  # 18位身份证号
            f"user_{i}",  # 用户名
            f"姓名_{i}",  # 真实姓名
            ''.join(random.choices(string.ascii_letters + string.digits, k=10)),  # 10位密码
            f"1{random.randint(3000000000, 3999999999)}",  # 11位手机号
            f"user_{i}@example.com",  # 邮箱
            random.randint(18, 60),  # 年龄
            random.choice(['男', '女']),  # 性别
            f"地址_{random.randint(1, 1000)}号",  # 地址
            date(2020 + random.randint(0, 4), random.randint(1, 12), random.randint(1, 28)),  # 入职日期
            round(random.uniform(5000, 20000), 2),  # 薪资
            random.choice(['工程师', '经理', '总监', '专员', '主管']),  # 职位
            random.choice(['A公司', 'B集团', 'C科技', 'D企业'])  # 公司
        ]
        data.append(row)
    return data


# ================= 写入Excel =================
def write_to_excel(data, filename="user_data.xlsx"):
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "用户信息"

    # 写入表头
    headers = [
        'id', 'idcard', 'username', 'realname', 'pwd',
        'telphone', 'email', 'age', 'sex', 'address',
        'hiredate', 'sal', 'job', 'company'
    ]
    for col_num, header in enumerate(headers, 1):
        col_letter = get_column_letter(col_num)
        ws[f"{col_letter}1"] = header

    # 写入数据
    for row_num, row_data in enumerate(data, 2):  # 从第2行开始
        for col_num, cell_value in enumerate(row_data, 1):
            col_letter = get_column_letter(col_num)
            ws[f"{col_letter}{row_num}"] = cell_value

    wb.save(filename)
    print(f"成功生成Excel文件: {filename}")


# ================= 读取Excel =================
def read_from_excel(filename="user_data.xlsx"):
    wb = openpyxl.load_workbook(filename)
    ws = wb.active

    data = []
    for row in ws.iter_rows(min_row=2, values_only=True):  # 跳过标题行
        data.append(row)

    print(f"成功读取Excel数据: {len(data)}条记录")
    return data


# ================= 写入MySQL =================
def write_to_mysql(data):
    # 数据库配置（修改为实际值）
    db_config = \
        {
            'host': 'localhost',
            'user': 'root',
            'port': 3306,
            'database': 'hr',
            'password': '123456789',
            'charset': 'utf8'
        }

    conn = None
    try:
        # 连接数据库
        conn = pymysql.connect(**db_config)
        cursor = conn.cursor()

        # 创建表（如果不存在）
        create_table_sql = """
        CREATE TABLE IF NOT EXISTS user_info (
            id INT PRIMARY KEY,
            idcard VARCHAR(50),
            username VARCHAR(50),
            realname VARCHAR(50),
            pwd VARCHAR(50),
            telphone VARCHAR(12),
            email VARCHAR(100),
            age INT,
            sex VARCHAR(20),
            address VARCHAR(200),
            hiredate DATE,
            sal DECIMAL(9,2),
            job VARCHAR(100),
            company VARCHAR(100)
        );
        """
        cursor.execute(create_table_sql)

        # 批量插入数据
        insert_sql = """
        INSERT INTO user_info (
            id, idcard, username, realname, pwd, telphone, 
            email, age, sex, address, hiredate, sal, job, company
        ) VALUES (
            %s, %s, %s, %s, %s, %s, 
            %s, %s, %s, %s, %s, %s, %s, %s
        )
        """
        cursor.executemany(insert_sql, data)
        conn.commit()
        print(f"成功导入数据库: {cursor.rowcount}条记录")

    except Exception as e:
        print(f"数据库操作失败: {e}")
    finally:
        if conn:
            conn.close()


# ================= 主程序 =================
if __name__ == "__main__":
    # 1. 生成模拟数据
    mock_data = generate_mock_data()

    # 2. 写入Excel文件
    write_to_excel(mock_data)

    # 3. 从Excel读取数据
    excel_data = read_from_excel()

    # 4. 写入MySQL数据库
    write_to_mysql(excel_data)